﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using EduFormManager.Models.Log;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Criterion;

namespace EduFormManager.Models.Repo
{
    public class ApplicationRepository : IRepository
    {
        ISession _session;
        ISessionFactory _sessionFactory;

        public ApplicationRepository()
        {
            Configuration configuration = new Configuration().Configure();

            Assembly assembly = Assembly.GetExecutingAssembly();
            configuration.AddAssembly(assembly);

            _sessionFactory = configuration.BuildSessionFactory();
            _session = _sessionFactory.OpenSession();
        }

        //public void Clear()
        //{
        //    _session.Clear();
        //    _sessionFactory.EvictQueries();
        //    foreach (var collectionMetadata in _sessionFactory.GetAllCollectionMetadata())
        //        _sessionFactory.EvictCollection(collectionMetadata.Key);
        //    foreach (var classMetadata in _sessionFactory.GetAllClassMetadata())
        //        _sessionFactory.EvictEntity(classMetadata.Key);
        //}

        public ISession Session { get { return _session; } }

        public Task SaveAsync(Entity obj)
        {
            return Task.Run(() => Save(obj));
        }

        public void Save(Entity obj)
        {
            lock (this)
            {
                ITransaction transaction = null;
                try
                {
                    transaction = _session.BeginTransaction();
                    _session.SaveOrUpdate(obj);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    if (transaction != null)
                        transaction.Rollback();
                    throw ex;
                }
            }
        }

        public void Delete(Entity obj)
        {
            lock (this)
            {
                ITransaction transaction = null;
                try
                {
                    transaction = _session.BeginTransaction();
                    if (obj is EduOrg)
                    {
                        ISQLQuery query = _session.CreateSQLQuery("DELETE FROM form_data WHERE edu_id = :edu_org_id");
                        query.SetInt32("edu_org_id", obj.Id);
                        query.ExecuteUpdate();

                        query = _session.CreateSQLQuery("DELETE FROM mm_edu__activity_type WHERE edu_id = :id");
                        query.SetInt32("id", obj.Id);
                        query.ExecuteUpdate();

                        query = _session.CreateSQLQuery("DELETE FROM mm_edu__management_agency WHERE edu_id = :id");
                        query.SetInt32("id", obj.Id);
                        query.ExecuteUpdate();

                        query = _session.CreateSQLQuery("DELETE FROM mm_edu__management_agency_activity WHERE edu_id = :id");
                        query.SetInt32("id", obj.Id);
                        query.ExecuteUpdate();

                        query = _session.CreateSQLQuery("DELETE FROM logs.edu_log WHERE edu_org_id = :id");
                        query.SetInt32("id", obj.Id);
                        query.ExecuteUpdate();

                        query = _session.CreateSQLQuery("DELETE FROM message WHERE from_edu_id = :id");
                        query.SetInt32("id", obj.Id);
                        query.ExecuteUpdate();

                        query = _session.CreateSQLQuery("DELETE FROM edu WHERE edu_id = :id");
                        query.SetInt32("id", obj.Id);
                        query.ExecuteUpdate();
                    }
                    else
                    {
                        Object merged = _session.Merge(obj);
                        _session.Delete(merged);
                    }
                    //else if (obj is Allform)
                    //{
                    //Allform form = obj as Allform;
                    //ISQLQuery query = _session.CreateSQLQuery("DELETE FROM file WHERE id in (:file_id_list)");
                    //IList<int> fileIdList = new List<int>();
                    //}

                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    if (transaction != null)
                        transaction.Rollback();
                    throw ex;
                }
            }
        }

        public Task<IList<FormStatisticSummary>> GetFormStatisticSummaryAsync(Municipality m)
        {
            return Task.Run(() => GetFormStatisticSummary(m));
        }

        public IList<FormStatisticSummary> GetFormStatisticSummary(Municipality m)
        {
            ICriteria crit = _session.CreateCriteria<FormStatisticSummary>()
                .Add(Restrictions.Eq("MunicipalityName", m.Name));
            return crit.List<FormStatisticSummary>();
        }

        public Task<IList<FormStatisticSummary>> GetFormStatisticSummaryAsync()
        {
            return Task.Run(() => GetFormStatisticSummary());
        }

        public IList<FormStatisticSummary> GetFormStatisticSummary()
        {
            return this.GetAll<FormStatisticSummary>();
        }

        public Task<IList<FormStatistic>> GetFormStatisticAsync(Municipality m)
        {
            return Task.Run(() => GetFormStatistic(m));
        }

        public IList<FormStatistic> GetFormStatistic(Municipality m)
        {
            ICriteria crit = _session.CreateCriteria<FormStatistic>()
                .Add(Restrictions.Eq("MunicipalityName", m.Name));
            return crit.List<FormStatistic>();
        }

        public Task<IList<FormStatistic>> GetFormStatisticAsync()
        {
            return Task.Run(() => GetFormStatistic());
        }

        public IList<FormStatistic> GetFormStatistic()
        {
            return this.GetAll<FormStatistic>();
        } 

        public Task<IList<DetailedFormStatistic>> GetDetailedFormStatisticsForYearAsync(int year)
        {
            return Task.Run(() => this.GetDetailedFormStatisticsForYear(year));
        }

        public Task<IList<DetailedFormStatistic>> GetDetailedFormStatisticsForYearAsync(int year, Municipality m)
        {
            return Task.Run(() => this.GetDetailedFormStatisticsForYear(year, m));
        }

        public IList<DetailedFormStatistic> GetDetailedFormStatisticsForYear(int year, Municipality m)
        {
            ICriteria crit = _session.CreateCriteria<DetailedFormStatistic>()
                .Add(Restrictions.Or(Restrictions.Eq("Year", year), Restrictions.Eq("Year", 0)))
                .Add(Restrictions.Eq("MunicipalityId", m.Id));
            return crit.List<DetailedFormStatistic>();
        }

        public IList<DetailedFormStatistic> GetDetailedFormStatisticsForYear(int year)
        {
            ICriteria crit = _session.CreateCriteria<DetailedFormStatistic>()
                .Add(Restrictions.Or(Restrictions.Eq("Year", year), Restrictions.Eq("Year", 0)));
            return crit.List<DetailedFormStatistic>();
        } 

        public MunicipalitySummaryFormula GetMunicipalitySummaryFormulaByMunicipalityForm(Allform form)
        {
            ICriteria crit = _session.CreateCriteria<MunicipalitySummaryFormula>()
                .Add(Restrictions.Eq("MunicipalityForm", form));
            return crit.List<MunicipalitySummaryFormula>().FirstOrDefault();
        }

        public DocumentFormat GetDocumentFormat(string title)
        {
            ICriteria crit = _session.CreateCriteria<DocumentFormat>()
                .Add(Restrictions.Eq("Name", title));
            return crit.UniqueResult<DocumentFormat>();
        }

        public IList<Allform> GetFormListByType(AllformType.Type.Types type)
        {
            AllformType formType = this.GetById<AllformType>((int) type);
            ICriteria crit = _session.CreateCriteria<Allform>()
                .Add(Restrictions.Eq("Type", formType));
            return crit.List<Allform>();
        }

        public TemplatedFormData SaveTemplate(TemplatedFormData template)
        {
            if (template.Id == 0)
            {
                ISQLQuery query = _session.CreateSQLQuery("INSERT INTO templated_form_data (form_id, file_data) VALUES (:form_id, :bytes) RETURNING templated_form_data_id");
                query.SetInt32("form_id", template.AllForm.Id);
                query.SetBinary("bytes", template.ExcelFileBytes);
                int id = (int) query.UniqueResult();
                return this.GetById<TemplatedFormData>(id);
            }
            else
            {
                ISQLQuery query = _session.CreateSQLQuery("UPDATE templated_form_data SET file_data = :bytes WHERE form_id = :form_id");
                query.SetInt32("form_id", template.AllForm.Id);
                query.SetBinary("bytes", template.ExcelFileBytes);
                query.ExecuteUpdate();
                int id = template.Id;
                return this.GetById<TemplatedFormData>(id);
            }
        }

        public void SaveCheckData(Allform form)
        {
            ISQLQuery query = _session.CreateSQLQuery("UPDATE form SET check_file_data = :bytes, check_file_md5 = :md5 WHERE form_id = :form_id");
            query.SetInt32("form_id", form.Id);
            query.SetBinary("bytes", form.CheckContents);
            query.SetString("md5", form.CheckfileMD5);
            query.ExecuteUpdate();
        }

        public T GetById<T>(int id) where T : class
        {
            lock (this)
            {
                ICriteria criteria = _session.CreateCriteria<T>();
                criteria.Add(Restrictions.Eq("Id", id));
                return criteria.UniqueResult<T>();
            }
        }

        public IList<T> GetAll<T>() where T : class
        {
            ICriteria criteria = _session.CreateCriteria<T>();
            IList<T> list = criteria.List<T>();
            return list;
        }

        public Task<IList<T>> GetAllAsync<T>() where T : class
        {
            return Task.Run(() => GetAll<T>());
        }

        public IList<T> GetBy<T>(IDictionary<string, object> properties) where T : class
        {
            ICriteria criteria = _session.CreateCriteria<T>();
            foreach (var entry in properties)
            {
                string property = entry.Key;
                object value = entry.Value;
                criteria.Add(Restrictions.Eq(property, value));
            }
            return criteria.List<T>();
        }

        public int GetEduOrgCount(Municipality m)
        {
            ICriteria crit = _session.CreateCriteria<EduOrg>()
                .Add(Restrictions.Eq("Municipality", m))
                .SetProjection(Projections.RowCount());
            return crit.UniqueResult<int>();
        }

        public EduOrg GetEduOrgByShortname(string name)
        {
            if (String.IsNullOrEmpty(name))
            {
                return null;
            }
            ICriteria criteria = _session.CreateCriteria<EduOrg>();
            criteria.Add(Restrictions.Eq("ShortName", name));
            IList<EduOrg> list = criteria.List<EduOrg>();
            return (list == null || list.Count == 0) ? null : list[0];
        }

        public EduOrg GetEduOrgByName(string name)
        {
            if (String.IsNullOrEmpty(name))
            {
                return null;
            }
            ICriteria criteria = _session.CreateCriteria<EduOrg>();
            criteria.Add(Restrictions.Eq("Name", name));
            IList<EduOrg> list = criteria.List<EduOrg>();
            return (list == null || list.Count == 0) ? null : list[0];
        }

        public Credentials GetCredentials(int ref_id, Credentials.Type type)
        {
            ICriteria criteria = _session.CreateCriteria<Credentials>();
            criteria.Add(Restrictions.Eq("ReferenceID", ref_id));
            criteria.Add(Restrictions.Eq("LoginType", type));
            return criteria.UniqueResult<Credentials>();
        }

        public Credentials GetCredentials(string login)
        {
            ICriteria criteria = _session.CreateCriteria<Credentials>();
            criteria.Add(Restrictions.Eq("Login", login));
            return criteria.UniqueResult<Credentials>();
        }

        public Credentials GetCredentials(string login, string password)
        {
            Credentials user = new Credentials();
            if (String.IsNullOrEmpty(login) || String.IsNullOrEmpty(password))
            {
                return null;
            }
            ICriteria criteria = _session.CreateCriteria<Credentials>();
            criteria.Add(Restrictions.Eq("Login", login));
            criteria.Add(Restrictions.Eq("Password", password));
            Credentials cred = criteria.UniqueResult<Credentials>();
            if (cred != null)
            {
                if (cred.IsEdu || cred.IsAdmin)
                {
                    cred.EduOrg = this.GetById<EduOrg>(cred.ReferenceID);
                }
                else if (cred.IsMunicipality)
                {
                    cred.Municipality = this.GetById<Municipality>(cred.ReferenceID);
                }
            }
            return cred;
        }

        public IList<SummaryView> GetSummary(bool isArchive = false)
        {
            ICriteria crit = isArchive ? _session.CreateCriteria<ArchiveSummaryView>() :
                _session.CreateCriteria<CurrentSummaryView>();

            crit.CreateAlias("Municipality", "munit")
                .CreateAlias("EduKind", "kind")
                .CreateAlias("Edu", "edu")
                .AddOrder(Order.Asc("munit.Name"))
                .AddOrder(Order.Asc("kind.KindName"))
                .AddOrder(Order.Asc("edu.ShortName"));
            return crit.List<SummaryView>();
        }

        public IList<MunicipalityFormData> GetMunitFormData(bool isArchive = false)
        {
            string sQuery = string.Format(@"SELECT * FROM municipality_form_data 
                                            WHERE date_part('year', send_date) {0} date_part('year', now())", (isArchive ? "<" : "="));
            ISQLQuery query = _session.CreateSQLQuery(sQuery);
            query.AddEntity(typeof(MunicipalityFormData));
            IList<MunicipalityFormData> result = query.List<MunicipalityFormData>();
            return result;
        }
        public IList<MunicipalityFormData> GetMunitFormData(Municipality m, bool isArchive = false)
        {
            string sQuery = string.Format(@"SELECT * FROM municipality_form_data 
                                            WHERE municipality_id = :munit_id AND date_part('year', send_date) {0} date_part('year', now())", (isArchive ? "<" : "="));
            ISQLQuery query = _session.CreateSQLQuery(sQuery);
            query.SetInt32("munit_id", m.Id);
            query.AddEntity(typeof(MunicipalityFormData));
            IList<MunicipalityFormData> result = query.List<MunicipalityFormData>();
            return result;
        }

        public IList<EduFormData> GetEduFormDataByMunit(Municipality m, AllformType.Type.Types type, bool isArchive)
        {
            string sQuery = String.Format(@"SELECT fd.* FROM form_data fd 
                                            JOIN edu e ON e.edu_id = fd.edu_id 
                                            JOIN municipality m ON m.municipality_id = e.municipality_id 
                                            JOIN form f on f.form_id = fd.form_id 
                                            WHERE date_part('year', fd.send_date) {0} date_part('year', now()) AND 
                                                f.form_type_id = :type_id and m.municipality_id = :munit_id", (isArchive ? "<" : "=")
                );
            ISQLQuery query = _session.CreateSQLQuery(sQuery);
            query.SetInt32("type_id", (int)type);
            query.SetInt32("munit_id", m.Id);
            query.AddEntity(typeof(EduFormData));
            return query.List<EduFormData>();
        }

        public IList<EduFormData> GetEduFormData(AllformType.Type.Types type, bool isArchive)
        {
            string sQuery = String.Format(@"SELECT fd.* FROM form_data fd 
                                            JOIN form f on f.form_id = fd.form_id 
                                            WHERE date_part('year', fd.send_date) {0} date_part('year', now()) AND 
                                                f.form_type_id = :type_id", (isArchive ? "<" : "=")
                );
            ISQLQuery query = _session.CreateSQLQuery(sQuery);
            query.SetInt32("type_id", (int)type);
            query.AddEntity(typeof(EduFormData));
            return query.List<EduFormData>();
        }

        public IList<EduFormData> GetEduFormData(EduOrg edu, AllformType.Type.Types type, bool isArchive = false)
        {
            string sQuery = string.Format(@"SELECT fd.* FROM form_data fd 
                                            JOIN form f on f.form_id = fd.form_id 
                                            WHERE fd.edu_id = :edu_id AND 
                                                date_part('year', send_date) {0} date_part('year', now()) AND 
                                                f.form_type_id = :type_id", isArchive ? "<" : "=");
            ISQLQuery query = _session.CreateSQLQuery(sQuery);
            query.SetInt32("edu_id", edu.Id);
            query.SetInt32("type_id", (int)type);
            query.AddEntity(typeof(EduFormData));
            IList<EduFormData> dataList = query.List<EduFormData>();
            return dataList;
        }

        public Task<EduFormData> GetFormDataAsync(EduOrg edu, Allform form, int year)
        {
            return Task.Run(() => this.GetFormData(edu, form, year));
        }
        public Task<MunicipalityFormData> GetFormDataAsync(Municipality munit, Allform form, int year)
        {
            return Task.Run(() => this.GetFormData(munit, form, year));
        }
        public IList<FormData> GetEduFormData(Allform form)
        {
            List<FormData> result = new List<FormData>();
            ICriteria eduCrit = _session.CreateCriteria<EduFormData>()
                .Add(Restrictions.Eq("Form", form));
            ICriteria munitCrit = _session.CreateCriteria<MunicipalityFormData>()
                .Add(Restrictions.Eq("Form", form));
            result.AddRange(eduCrit.List<FormData>());
            result.AddRange(munitCrit.List<FormData>());
            return result;
        }
        public MunicipalityFormData GetFormData(Municipality munit, Allform form, int year)
        {
            ISQLQuery query = _session.CreateSQLQuery("SELECT * FROM municipality_form_data WHERE form_id = :allform_id AND municipality_id = :munit_id AND cast(date_part('year', send_date) as integer) = :year");
            query.SetInt32("allform_id", form.Id);
            query.SetInt32("munit_id", munit.Id);
            query.SetInt32("year", year);
            query.AddEntity(typeof(MunicipalityFormData));
            MunicipalityFormData result = query.UniqueResult<MunicipalityFormData>();
            return result;
        }
        public EduFormData GetFormData(EduOrg edu, Allform form, int year)
        {
            ISQLQuery query = _session.CreateSQLQuery("SELECT * FROM form_data WHERE form_id = :allform_id AND edu_id = :eduorg_id AND cast(date_part('year', send_date) as integer) = :year");
            query.SetInt32("allform_id", form.Id);
            query.SetInt32("eduorg_id", edu.Id);
            query.SetInt32("year", year);
            query.AddEntity(typeof(EduFormData));
            EduFormData result = query.UniqueResult<EduFormData>();
            return result;
        }
        
        public IList<Allform> GetRemainingFormsWithoutTemplate()
        {
            ISQLQuery query = _session.CreateSQLQuery("SELECT * FROM form WHERE form_id NOT IN (SELECT form_id FROM templated_form_data)");
            query.AddEntity(typeof(Allform));
            return query.List<Allform>();
        }

        public IList<Allform> GetRemainingFormsWithTemplate()
        {
            ISQLQuery query = _session.CreateSQLQuery("SELECT * FROM form WHERE id IN (SELECT form_id FROM templated_form_data)");
            query.AddEntity(typeof(Allform));
            return query.List<Allform>();
        }

        public Task<TemplatedFormData> GetTemplatedFormDataAsync(Allform form)
        {
            return Task.Run(() => this.GetTemplatedFormData(form));
        }

        public TemplatedFormData GetTemplatedFormData(Allform form)
        {
            ICriteria criteria = _session.CreateCriteria<TemplatedFormData>();
            criteria.Add(Restrictions.Eq("AllForm", form));
            return criteria.UniqueResult<TemplatedFormData>();
        }

        public Task<IList<EduOrg>> GetEduOrgsHaveFormDataAsync(Allform form, int year)
        {
            return Task.Run(() => this.GetEduOrgsHaveFormData(form, year));
        }
        public IList<EduOrg> GetEduOrgsHaveFormData(Allform form, int year)
        {
            ISQLQuery query = _session.CreateSQLQuery(@"select eo.* from edu eo join form_data fd on fd.edu_id = eo.edu_id and 
                                                            date_part('year', fd.send_date) = :year and 
                                                            fd.form_id = :form_id");
            query.SetInt32("form_id", form.Id);
            query.SetInt32("year", year);
            query.AddEntity(typeof (EduOrg));
            return query.List<EduOrg>();
        }

        public Task<IList<Query>> GetQueriesAsync(Credentials cred)
        {
            return Task.Run(() => GetQueries(cred));
        }

        public IList<Query> GetQueries(Credentials cred)
        {
            ICriteria criteria = _session.CreateCriteria<Query>();
            criteria.Add(Restrictions.Eq("Credentials", cred));
            return criteria.List<Query>();
        }

        public Task<IList<Query>> GetQueriesByFormAsync(Allform form, Credentials cred)
        {
            return Task.Run(() => this.GetQueriesByForm(form, cred));
        }
        public IList<Query> GetQueriesByForm(Allform form, Credentials cred)
        {
            ICriteria criteria = _session.CreateCriteria<Query>();
            criteria.Add(Restrictions.Eq("AllForm", form));
            criteria.Add(Restrictions.Eq("Credentials", cred));
            return criteria.List<Query>();
        }

        public Task<IList<Allform>> GetFormsHaveQueriesAsync()
        {
            return Task.Run(() => this.GetFormsHaveQueries());
        }

        public IList<Allform> GetFormsHaveQueries()
        {
            ISQLQuery query = _session.CreateSQLQuery(@"select distinct f.* from form f join query q on q.form_id = f.form_id");
            query.AddEntity(typeof (Allform));
            return query.List<Allform>();
        } 

        public Task<IList<int>>  GetAvailableYearsAsync()
        {
            return Task.Run(() => this.GetAvailableYears());
        }
        public IList<int> GetAvailableYears()
        {
            ISQLQuery query = _session.CreateSQLQuery("SELECT DISTINCT cast(date_part('year', send_date) as integer) FROM form_data");
            return query.List<int>();
        }

        public DateTime GetServerDate() 
        {
            IQuery query = _session.CreateSQLQuery("SELECT now()");
            object result = query.UniqueResult();
            return (DateTime)result;
        }

        public Task<int> GetNewMessagesCountAsync()
        {
            return Task.Run<int>(() => this.GetNewMessagesCount());
        }

        public int GetNewMessagesCount()
        {
            ICriteria criteria = _session.CreateCriteria<Message>()
                .Add(Restrictions.Eq("IsViewed", false))
                .SetProjection(Projections.RowCount());
            return (int)criteria.UniqueResult();
        }

        public IList<Message> GetMessages()
        {
            ICriteria criteria = _session.CreateCriteria<Message>()
                .AddOrder(Order.Desc("SendDate"));
            return criteria.List<Message>();
        }

        public IList<Log.Log> GetSignInOutLogs()
        {
            ICriteria criteria = _session.CreateCriteria<Log.Log>().
                Add(Restrictions.Or(
                        Restrictions.Eq("Action.Id", (int)LogAction.ActionType.Login),
                        Restrictions.Eq("Action.Id", (int)LogAction.ActionType.Logout)
                    )
                );
            return criteria.List<Log.Log>();
        }
    }
}
